/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.unesp.demac.soo.dao;

import br.unesp.demac.bcc.soo.bean.Dvd;
import br.unesp.demac.bcc.soo.bean.Modalidade;
import br.unesp.demac.bcc.soo.bean.Pessoa;
import br.unesp.demac.soo.util.FabricaConexao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author flavioam
 */
public class DvdDAOImp implements DvdDAO {

    @Override
    public Dvd selecionaDvdById(int id) {

        Dvd d = null;
        Connection con = null;

        try {

            con = FabricaConexao.getConexao();

            String sql = "select * from Dvds where idDvds = ?";

            PreparedStatement pstmt = con.prepareStatement(sql);

            pstmt.setInt(1, id);

            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {

                d = new Dvd();

                d.setClassificacao(rs.getInt("classificacao"));
                d.setDataCadastro(new java.util.Date(rs.getDate("dataCadastro").getTime()));
                d.setGenero(rs.getString("genero"));
                d.setIdDvd(rs.getInt("idDvds"));
                d.setNome(rs.getString("nome"));

                ModalidadeDAO mDao = new ModalidadeDAOImp();
                Modalidade m = mDao.selecionaModalidadeById(rs.getInt("Modalidades_idModalidades"));

                d.setModalidade(m);
            }

        } catch (SQLException e) {

            e.printStackTrace();

        } catch (Exception e) {

            e.printStackTrace();

        } finally {
            FabricaConexao.fechar(con);
            return d;
        }
    }

    public ArrayList<Dvd> selecionaRanking(Date inicio, Date fim, int numDvds) {

        Connection con = null;
        ArrayList<Dvd> l = new ArrayList<Dvd>();

        try {
            
            con = FabricaConexao.getConexao();

            String limit = "";

            if (numDvds != 0) {

                limit = "LIMIT 0," + numDvds;
            }

            String sql = "select count(*), Dvds_idDvds from RelAlugueisDvds "
                        + "where dataDevolucao between ? and ? "
                        + "group by Dvds_idDvds "
                        + "order by count(*) desc "+ limit;
            
            System.out.println(sql);
            
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setDate(1, new java.sql.Date(inicio.getTime()));
            pstm.setDate(2, new java.sql.Date(fim.getTime()));
            
            ResultSet rs = pstm.executeQuery();
            
            DvdDAO dDao = new DvdDAOImp();
            
            while(rs.next()){
            
                Dvd d = dDao.selecionaDvdById(rs.getInt("Dvds_idDvds"));
                
                l.add(d);
            }

        } catch (SQLException e) {

            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            FabricaConexao.fechar(con);
            return l;
        }
    }
}
